package com.ws.shop.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.ArticleCategoriesDao;
import com.ws.shop.entity.Article;
import com.ws.shop.entity.ArticleCategories;
import com.ws.shop.util.JdbcUtil;
import com.ws.shop.util.StringUtil;

@Repository(value="articleCategoriesDao")
public class ArticleCategoriesDaoImpl extends BaseDaoImpl implements ArticleCategoriesDao {

	final String findArticleCategoriesListAll = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories";
	public List<ArticleCategories> findArticleCategoriesListAll() {
		return this.query(findArticleCategoriesListAll, new ResultSetExtractor<List<ArticleCategories>>(){
			public List<ArticleCategories> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				ArticleCategories ac = null;
				while(rs.next()){
					ac = new ArticleCategories();
					ac.setId(rs.getInt(1));
					ac.setName(rs.getString(2));
					ac.setPath(rs.getString(3));
					ac.setSingle(rs.getInt(4));
					ac.setFid(rs.getInt(5));
					ac.setOrd(rs.getInt(6));
					ac.setType(rs.getInt(7));
					ac.setAddtime(rs.getTimestamp(8));
					list.add(ac);
				}
				return list;
			}
		});
	}

	
	public List<ArticleCategories> findCategoriesByfid(final long fid){
		String s = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories where fid = " + fid;
		return this.query(s, new ResultSetExtractor<List<ArticleCategories>>(){
			public List<ArticleCategories> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				ArticleCategories ac = null;
				while(rs.next()){
					ac = new ArticleCategories();
					ac.setId(rs.getInt(1));
					ac.setName(rs.getString(2));
					ac.setPath(rs.getString(3));
					ac.setSingle(rs.getInt(4));
					ac.setFid(rs.getInt(5));
					ac.setOrd(rs.getInt(6));
					ac.setType(rs.getInt(7));
					ac.setAddtime(rs.getTimestamp(8));
					list.add(ac);
				}
				return list;
			}
		});
	}

	String save_sql = "insert into jvf_article_categories(name,path,single,fid,ord,type,addtime) values(?,?,?,?,?,?,now())";
	String update_sql = "update jvf_article_categories set name=?,fid=? where id=?";
	public void saveOrUpdate(ArticleCategories articleCategories) {
		if(articleCategories.getId() == 0){
			this.update(save_sql,articleCategories.getName(),articleCategories.getPath(),articleCategories.getSingle(),articleCategories.getFid(),articleCategories.getType(),articleCategories.getOrd());
		}else{
			this.update(update_sql,articleCategories.getName(),articleCategories.getFid(),articleCategories.getId());
		}
	}


	final String findArticleCategoriesListByPage_count = "select count(1) from jvf_article_categories";
	final String findArticleCategoriesListByPage_list = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories";
	final String findArticleCategoriesListByPage_order = " order by id desc";
	public Page findArticleCategoriesListByPage(int pageno, int pagesize,
			String name) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(name != null){
			query.append(" and name='").append(name).append("'");
		}
		return findPage(pageno,pagesize,new StringBuilder(findArticleCategoriesListByPage_count).append(query).toString()
				,new StringBuilder(findArticleCategoriesListByPage_list).append(query).append(findArticleCategoriesListByPage_order));
	}


	final String findArticleCategoriesById = "select name,path,single,fid,ord,type,addtime from jvf_article_categories where id=?";
	public ArticleCategories findArticleCategoriesById(final int id) {
		return this.query(findArticleCategoriesById,new ResultSetExtractor<ArticleCategories>(){
			public ArticleCategories extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				if(!rs.next())
					return null;
				ArticleCategories a = new ArticleCategories();
				a.setId(id);
				a.setName(rs.getString(1));
				a.setPath(rs.getString(2));
				a.setSingle(rs.getInt(3));
				a.setFid(rs.getInt(4));
				a.setOrd(rs.getInt(5));
				a.setType(rs.getInt(6));
				a.setAddtime(rs.getTimestamp(7));
				return a;
			}
		},id);
	}


	final String findAllChildArticleCategoriesById = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories";
	public List<ArticleCategories> findAllChildArticleCategoriesById(final int id,
			final int single) {
		return this.execute(new StatementCallback<List<ArticleCategories>>(){
			public List<ArticleCategories> doInStatement(Statement stmt)
					throws SQLException, DataAccessException {
				StringBuilder query = new StringBuilder(" where 1=1");
				if(single >= 0){
					query.append(" and single=").append(single);
				}
				query.append(" and path like '%").append(id).append("%'");
				ResultSet rs = null;
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				try{
					rs = stmt.executeQuery(new StringBuilder(findAllChildArticleCategoriesById).append(query).toString());
					ArticleCategories a = null;
					while(rs.next()){
						a = new ArticleCategories();
						a.setId(rs.getInt(1));
						a.setName(rs.getString(2));
						a.setPath(rs.getString(3));
						a.setSingle(rs.getInt(4));
						a.setFid(rs.getInt(5));
						a.setOrd(rs.getInt(6));
						a.setType(rs.getInt(7));
						a.setAddtime(rs.getTimestamp(8));
						list.add(a);
					}
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
				return list;
			}
		});
	}

	final String findArticleCategoriesTreeList = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories order by ord";
	public List<ArticleCategories> findArticleCategoriesTreeList() {
		return this.query(findArticleCategoriesTreeList, new ResultSetExtractor<List<ArticleCategories>>(){
			public List<ArticleCategories> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				ArticleCategories ac = null;
				Map<Integer,ArticleCategories> map = new HashMap<Integer,ArticleCategories>();
				while(rs.next()){
					ac = new ArticleCategories();
					ac.setId(rs.getInt(1));
					ac.setName(rs.getString(2));
					ac.setPath(rs.getString(3));
					ac.setSingle(rs.getInt(4));
					ac.setFid(rs.getInt(5));
					ac.setOrd(rs.getInt(6));
					ac.setType(rs.getInt(7));
					ac.setAddtime(rs.getTimestamp(8));
					list.add(ac);
					map.put(ac.getId(), ac);
				}
				//填充子类型
				Collection<ArticleCategories> coll = map.values();
				ArticleCategories p = null;
				for(ArticleCategories a : coll){
					p = map.get(a.getFid());
					if(p != null){
						p.getChildren().add(a);
						list.remove(a);
					}	
				}
				return list;
			}
		});
	}

	public List<ArticleCategories> findArticleCategoriesParentTreeById(final int id) {
		return this.execute(new StatementCallback<List<ArticleCategories>>(){
			public List<ArticleCategories> doInStatement(Statement stmt)
					throws SQLException, DataAccessException {
				String sql = "select path from jvf_article_categories where id="+id;
				ResultSet rs = null;
				try{
					rs = stmt.executeQuery(sql);
					String path = null;
					if(!rs.next()){
						return null;
					}
					path = rs.getString(1);
					if(StringUtil.isEmpty(path)){
						return null;
					}
					JdbcUtil.closeResultSet(rs);
					sql = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories where id in("+path+") order by path";
					rs = stmt.executeQuery(sql);
					List<ArticleCategories> list = new LinkedList<ArticleCategories>();
					ArticleCategories ac = null;
					while(rs.next()){
						ac = new ArticleCategories();
						ac.setId(rs.getInt(1));
						ac.setName(rs.getString(2));
						ac.setPath(rs.getString(3));
						ac.setSingle(rs.getInt(4));
						ac.setFid(rs.getInt(5));
						ac.setOrd(rs.getInt(6));
						ac.setType(rs.getInt(7));
						ac.setAddtime(rs.getTimestamp(8));
						list.add(ac);
					}
					return list;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}

	final String findChildArticleCategoriesTreeById = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories";
	public List<ArticleCategories> findChildArticleCategoriesTreeById(final int id) {
		return this.execute(new StatementCallback<List<ArticleCategories>>(){
			public List<ArticleCategories> doInStatement(Statement stmt)
					throws SQLException, DataAccessException {
				StringBuilder query = new StringBuilder(" where 1=1");
				query.append(" and (path like '").append(id).append(ArticleCategories.PATH_SPACTOR)
					.append("%' or path like '%").append(ArticleCategories.PATH_SPACTOR)
					.append(id).append(ArticleCategories.PATH_SPACTOR).append("%' or path like '%")
					.append(ArticleCategories.PATH_SPACTOR).append(id).append(")");
				ResultSet rs = null;
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				try{
					rs = stmt.executeQuery(new StringBuilder(findChildArticleCategoriesTreeById).append(query).toString());
					ArticleCategories ac = null;
					Map<Integer,ArticleCategories> map = new HashMap<Integer,ArticleCategories>();
					while(rs.next()){
						ac = new ArticleCategories();
						ac.setId(rs.getInt(1));
						ac.setName(rs.getString(2));
						ac.setPath(rs.getString(3));
						ac.setSingle(rs.getInt(4));
						ac.setFid(rs.getInt(5));
						ac.setOrd(rs.getInt(6));
						ac.setType(rs.getInt(7));
						ac.setAddtime(rs.getTimestamp(8));
						list.add(ac);
						map.put(ac.getId(), ac);
					}
					//填充子类型
					Collection<ArticleCategories> coll = map.values();
					ArticleCategories p = null;
					for(ArticleCategories a : coll){
						p = map.get(a.getFid());
						if(p != null){
							p.getChildren().add(a);
							list.remove(a);
						}	
					}
					return list;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
				
			}
		});
	}

	final String findChildArticleCategoriesListPage_count = "select count(1) from jvf_article_categories";
	final String findChildArticleCategoriesListPage_list = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories";
	final String findChildArticleCategoriesListPage_order = " order by ord";
	public Page findChildArticleCategoriesListPage(int pageno, int pagesize,
			int fid) {
		StringBuilder query = new StringBuilder(" where fid=").append(fid);
		return findPage(pageno,pagesize,new StringBuilder(findChildArticleCategoriesListPage_count).append(query).toString()
				,new StringBuilder(findChildArticleCategoriesListPage_list).append(query).append(findChildArticleCategoriesListPage_order));
	}

	
	public List<ArticleCategories> findChildArticleCategoriesWithArticleById(
			final int id) {
		return this.execute(new StatementCallback<List<ArticleCategories>>(){
			public List<ArticleCategories> doInStatement(Statement stmt)
					throws SQLException, DataAccessException {
				String sql = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories where path like '%"+id+"%' order by ord";
				ResultSet rs = null;
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				try{
					rs = stmt.executeQuery(sql);
					ArticleCategories ac = null;
					Map<Integer,ArticleCategories> map = new LinkedHashMap<Integer,ArticleCategories>();
					StringBuilder singles = new StringBuilder();
					while(rs.next()){
						ac = new ArticleCategories();
						ac.setId(rs.getInt(1));
						ac.setName(rs.getString(2));
						ac.setPath(rs.getString(3));
						ac.setSingle(rs.getInt(4));
						ac.setFid(rs.getInt(5));
						ac.setOrd(rs.getInt(6));
						ac.setType(rs.getInt(7));
						ac.setAddtime(rs.getTimestamp(8));
						list.add(ac);
						map.put(ac.getId(), ac);
						if(ac.getSingle() == 1){
							singles.append(ac.getId()).append(",");
						}
					}
					if(singles.length() > 0){
						singles.deleteCharAt(singles.length()-1);
					}
					JdbcUtil.closeResultSet(rs);
					//填充子类型
					Collection<ArticleCategories> coll = map.values();
					ArticleCategories p = null;
					for(ArticleCategories acc : coll){
						p = map.get(acc.getFid());
						if(p != null){
							p.getChildren().add(acc);
							list.remove(acc);
						}	
					}
					sql = "select a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state from jvf_article a where a.state=1 and a.cid in("+singles.toString()+") order by `order`";
					rs = stmt.executeQuery(sql);
					Article a = null;
					while(rs.next()){
						a = new Article();
						a.setId(rs.getInt(1));
						a.setCid(rs.getInt(2));
						a.setTitle(rs.getString(3));
						a.setLink(rs.getString(4));
						a.setOrder(rs.getInt(5));
						a.setKeywords(rs.getString(6));
						a.setDescription(rs.getString(7));
						a.setEdittime(rs.getTimestamp(8));
						a.setBody(rs.getString(9));
						a.setState(rs.getInt(10));
						map.get(a.getCid()).getArticleList().add(a);
					}
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
				return list;
			}
		});
	}


	public List<ArticleCategories> findBottomNavigationWithArticle(final String acids) {
		return this.execute(new StatementCallback<List<ArticleCategories>>(){
			public List<ArticleCategories> doInStatement(Statement stmt)
					throws SQLException, DataAccessException {
				String sql = "select id,name,path,single,fid,ord,type,addtime from jvf_article_categories where id in("+acids+") and single=1 order by ord";
				ResultSet rs = null;
				List<ArticleCategories> list = new LinkedList<ArticleCategories>();
				try{
					rs = stmt.executeQuery(sql);
					ArticleCategories ac = null;
					Map<Integer,ArticleCategories> map = new LinkedHashMap<Integer,ArticleCategories>();
					while(rs.next()){
						ac = new ArticleCategories();
						ac.setId(rs.getInt(1));
						ac.setName(rs.getString(2));
						ac.setPath(rs.getString(3));
						ac.setSingle(rs.getInt(4));
						ac.setFid(rs.getInt(5));
						ac.setOrd(rs.getInt(6));
						ac.setType(rs.getInt(7));
						ac.setAddtime(rs.getTimestamp(8));
						list.add(ac);
						map.put(ac.getId(), ac);
					}
					JdbcUtil.closeResultSet(rs);
					sql = "select a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state from jvf_article a where a.state=1 and a.cid in("+acids+") order by `order`";
					rs = stmt.executeQuery(sql);
					Article a = null;
					while(rs.next()){
						a = new Article();
						a.setId(rs.getInt(1));
						a.setCid(rs.getInt(2));
						a.setTitle(rs.getString(3));
						a.setLink(rs.getString(4));
						a.setOrder(rs.getInt(5));
						a.setKeywords(rs.getString(6));
						a.setDescription(rs.getString(7));
						a.setEdittime(rs.getTimestamp(8));
						a.setBody(rs.getString(9));
						a.setState(rs.getInt(10));
						map.get(a.getCid()).getArticleList().add(a);
					}
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
				return list;
			}
		});
	}
	
}
